/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package stis.statpro.ibs.dao;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import stis.statpro.ibs.datasource.Datasource;
import stis.statpro.ibs.datasource.QueryClass;
import stis.statpro.ibs.model.Perusahaan;

/**
 *
 * @author imam habib pammungkas <imam habib pamungkas@gmail.com>
 */
public class PerusahaanDao implements PerusahaanDoaInface {

    private List<Perusahaan> listPerusahaan = new ArrayList<Perusahaan>();

    
    @Override
    public List<Perusahaan> getPerusahaanByProp(String kodeProp) throws SQLException {
        getQuery(kodeProp, QueryClass.SELECTBYPROP, 2);
        return listPerusahaan;
    }

    @Override
    public List<Perusahaan> getPerusahaanByKab(String kodeKab) throws SQLException {
        getQuery(kodeKab, QueryClass.SELECTBYKAB, 1);
        return listPerusahaan;
    }

    @Override
    public List<Perusahaan> getPerusahaanByNama(String nama) throws SQLException {
        getQuery(nama, QueryClass.SELECTBYNAMA, 3);
        return listPerusahaan;
    }

    @Override
    public void savePerusahaan(Perusahaan perusahaan) throws SQLException {
        PreparedStatement statement = Datasource.getConnection().prepareStatement(QueryClass.SAVEDATA);
        statement.setInt(1, perusahaan.getKip());
        statement.setString(2, perusahaan.getPropinsi());
        statement.setString(3, perusahaan.getKabupaten());
        statement.setString(4, perusahaan.getKotaAdministratif());
        statement.setString(5, perusahaan.getKecamatan());
        statement.setString(6, perusahaan.getDesaKelurahan());
        statement.setString(7, perusahaan.getNamaPrsh());
        statement.setString(8, perusahaan.getAlamatPrsh());
        statement.setString(9, perusahaan.getKodeposPrsh());
        statement.setString(10, perusahaan.getEmailPrsh());
        statement.setString(11, perusahaan.getTelpPrsh());
        statement.setString(12, perusahaan.getFaxPrsh());
        statement.setString(13, perusahaan.getAlamatKantorPusat());
        statement.setString(14, perusahaan.getKodeposKantorPusat());
        statement.setString(15, perusahaan.getEmailKantorPusat());
        statement.setString(16, perusahaan.getTelpKantorPusat());
        statement.setString(17, perusahaan.getFaxKantorPusat());
        statement.setString(18, perusahaan.getStatusPenanamanModal());
        statement.setString(19, perusahaan.getLokasiPrsh());
        statement.setBoolean(20, perusahaan.isAsetBangunan());
        statement.setBoolean(21, perusahaan.isAsetKendaraan());
        statement.setBoolean(22, perusahaan.isAsetMesindanAlatBerat());
        statement.setBoolean(23, perusahaan.isAsetGadget());
        statement.setBoolean(24, perusahaan.isAsetLainnya());
        statement.setLong(25, perusahaan.getAsetDalamRupiah());
        statement.setInt(26, perusahaan.getTenagakerja());
        statement.setLong(27, perusahaan.getOmzetsebulanDalamRupiah());
        statement.setString(28,perusahaan.getTanggalInput());
        statement.setBoolean(29, perusahaan.isUpdateData());
        statement.executeUpdate();
        listPerusahaan.add(perusahaan);
        statement.close();
    }

    @Override
    public void deletePerusahaan(int ind) throws SQLException {
        PreparedStatement statement = Datasource.getConnection().prepareStatement(QueryClass.DELETEDATA);
        statement.setInt(1, ind);
        statement.executeUpdate();
        statement.close();
    }

    @Override
    public void updatePerusahaan(int id) throws SQLException {
        PreparedStatement statement = Datasource.getConnection().prepareStatement(QueryClass.GETUPDATE);
        statement.setInt(1, id);
        statement.executeUpdate();
        statement.close();
    }

    public List<Perusahaan> getAllData() throws SQLException {
        PreparedStatement statement = Datasource.getConnection().prepareCall(QueryClass.SELECTALLDATA);
        ResultSet rs = statement.executeQuery();
        while (rs.next()) {
            Perusahaan p = new Perusahaan();
            p.setKip(rs.getInt(1));
            p.setPropinsi(rs.getString(2));
            p.setKabupaten(rs.getString(3));
            p.setKotaAdministratif(rs.getString(4));
            p.setKecamatan(rs.getString(5));
            p.setDesaKelurahan(rs.getString(6));

            p.setNamaPrsh(rs.getString(7));
            p.setAlamatPrsh(rs.getString(8));
            p.setKodeposPrsh(rs.getString(9));
            p.setEmailPrsh(rs.getString(10));
            p.setTelpPrsh(rs.getString(11));
            p.setFaxPrsh(rs.getString(12));
            p.setAlamatKantorPusat(rs.getString(13));
            p.setKodeposKantorPusat(rs.getString(14));
            p.setEmailKantorPusat(rs.getString(15));
            p.setTelpKantorPusat(rs.getString(16));
            p.setFaxKantorPusat(rs.getString(17));

            p.setStatusPenanamanModal(rs.getString(18));
            p.setLokasiPrsh(rs.getString(19));
            p.setAsetBangunan(rs.getBoolean(20));
            p.setAsetKendaraan(rs.getBoolean(21));
            p.setAsetMesindanAlatBerat(rs.getBoolean(22));
            p.setAsetGadget(rs.getBoolean(23));
            p.setAsetLainnya(rs.getBoolean(24));
            p.setAsetDalamRupiah(rs.getLong(25));
            p.setTenagakerja(rs.getInt(26));
            p.setOmzetsebulanDalamRupiah(rs.getLong(27));
            p.setTanggalInput(rs.getString(28));
            listPerusahaan.add(p);
        }
        statement.close();
        return listPerusahaan;
    }

    @Override
    public Perusahaan getPerusahaanByKip(int kip) throws SQLException {
        Perusahaan po=null;
        getQuery(kip, QueryClass.SELECTBYKIP);
        if(listPerusahaan.size()==0){
            return null;
        }else{
            for(Perusahaan p:listPerusahaan){
                if(p.isUpdateData()){
                   po=p;
                   //break;
                }
            }
            return po;
        }
    }

    private void getQuery(String i, String key, int x) throws SQLException {
        ResultSet rs = null;
        PreparedStatement statement=null;
        switch (x) {
            case 1:
                statement = Datasource.getConnection().prepareStatement(key);
                statement.setString(1, "%"+i+"%");
                rs = statement.executeQuery();
                break;
            case 2:
                statement = Datasource.getConnection().prepareStatement(key);
                statement.setString(1, "%"+i+"%");
                rs = statement.executeQuery();
                break;
            case 3:
                statement = Datasource.getConnection().prepareStatement(key);
                statement.setString(1, "%"+i+"%");
                rs = statement.executeQuery();
                break;
        }

        while (rs.next()) {
            Perusahaan p = new Perusahaan();
            p.setKip(rs.getInt(1));
            p.setPropinsi(rs.getString(2));
            p.setKabupaten(rs.getString(3));
            p.setKotaAdministratif(rs.getString(4));
            p.setKecamatan(rs.getString(5));
            p.setDesaKelurahan(rs.getString(6));

            p.setNamaPrsh(rs.getString(7));
            p.setAlamatPrsh(rs.getString(8));
            p.setKodeposPrsh(rs.getString(9));
            p.setEmailPrsh(rs.getString(10));
            p.setTelpPrsh(rs.getString(11));
            p.setFaxPrsh(rs.getString(12));
            p.setAlamatKantorPusat(rs.getString(13));
            p.setKodeposKantorPusat(rs.getString(14));
            p.setEmailKantorPusat(rs.getString(15));
            p.setTelpKantorPusat(rs.getString(16));
            p.setFaxKantorPusat(rs.getString(17));

            p.setStatusPenanamanModal(rs.getString(18));
            p.setLokasiPrsh(rs.getString(19));
            p.setAsetBangunan(rs.getBoolean(20));
            p.setAsetKendaraan(rs.getBoolean(21));
            p.setAsetMesindanAlatBerat(rs.getBoolean(22));
            p.setAsetGadget(rs.getBoolean(23));
            p.setAsetLainnya(rs.getBoolean(24));
            p.setAsetDalamRupiah(rs.getLong(25));
            p.setTenagakerja(rs.getInt(26));
            p.setOmzetsebulanDalamRupiah(rs.getLong(27));
            p.setTanggalInput(rs.getString(28));
            p.setUpdateData(rs.getBoolean(30));
            listPerusahaan.add(p);
        }
        statement.close();
    }

    private void getQuery(int i, String key) throws SQLException {
        PreparedStatement statement = Datasource.getConnection().prepareStatement(key);
        statement.setInt(1, i);
        ResultSet rs = statement.executeQuery();
        while (rs.next()) {
            Perusahaan p = new Perusahaan();
            p.setKip(rs.getInt(1));
            p.setPropinsi(rs.getString(2));
            p.setKabupaten(rs.getString(3));
            p.setKotaAdministratif(rs.getString(4));
            p.setKecamatan(rs.getString(5));
            p.setDesaKelurahan(rs.getString(6));

            p.setNamaPrsh(rs.getString(7));
            p.setAlamatPrsh(rs.getString(8));
            p.setKodeposPrsh(rs.getString(9));
            p.setEmailPrsh(rs.getString(10));
            p.setTelpPrsh(rs.getString(11));
            p.setFaxPrsh(rs.getString(12));
            p.setAlamatKantorPusat(rs.getString(13));
            p.setKodeposKantorPusat(rs.getString(14));
            p.setEmailKantorPusat(rs.getString(15));
            p.setTelpKantorPusat(rs.getString(16));
            p.setFaxKantorPusat(rs.getString(17));

            p.setStatusPenanamanModal(rs.getString(18));
            p.setLokasiPrsh(rs.getString(19));
            p.setAsetBangunan(rs.getBoolean(20));
            p.setAsetKendaraan(rs.getBoolean(21));
            p.setAsetMesindanAlatBerat(rs.getBoolean(22));
            p.setAsetGadget(rs.getBoolean(23));
            p.setAsetLainnya(rs.getBoolean(24));
            p.setAsetDalamRupiah(rs.getLong(25));
            p.setTenagakerja(rs.getInt(26));
            p.setOmzetsebulanDalamRupiah(rs.getLong(27));
            p.setTanggalInput(rs.getString(28));
            p.setUpdateData(rs.getBoolean(30));
            listPerusahaan.add(p);
        }
        statement.close();
    }

    @Override
    public List<Perusahaan> getPerusahaanByKipDelete(int kip) throws SQLException {
        getQuery(kip, QueryClass.SELECTBYKIP);
        if(listPerusahaan.size()==0){
            return null;
        }else{
            return listPerusahaan;
        }
    }


}
